package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

import static com.hualude.roadsign.service.impl.CountServiceImpl.convertParams;

/**
 * Created by ding on 2017/9/20.
 */
public class SolarEnergyFaultCountRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<String> getDealCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.SolarMaintenanceTime from tb_solar_fault m,tb_user u where m.SolarMaintenanceStatus = 2 and " +
                "(m.SolarMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.SolarMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getReportCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select s.SolarTime from tb_solar_fault s where " +
                "(s.SolarTime between '" + year + "' and '" + yearNext + "') and  (s.SolarOrgId in (" + orgIds + ") or " +
                "s.SolarMainOrgId in (" + orgIds + "))";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<FaultCountDto> getExpenseCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.SolarMaintenanceTime AS maintenanceTime,m.SolarMoney AS repairAmount from tb_solar_fault m,tb_user u where " +
                "(m.SolarMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.SolarMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(FaultCountDto.class));
        List<FaultCountDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<SolarEnergyFaultDto> getFault4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select f.SolarFaultId,f.SolarEnergyId,f.SolarPhoto,p.SolarEnergyPhoto,f.SolarLevel,f.SolarCheck,f.SolarMainCheck,\n" +
                "f.SolarTime,f.SolarCheckTime,o.OrgName AS solarEnergyOrgName,o2.orgName as solarEnergyMainOrgName,p.solarEnergyPeriod," +
                " p.SolarEnergyRoadName,p.SolarEnergyStartRoad,p.SolarEnergyEndRoad,p.SolarEnergyType,p.SolarEnergyMaterial,\n" +
                "p.SolarEnergyArea,p.SolarEnergyCity,p.SolarEnergyProvince,p.SolarEnergyStyle,u.UserName AS solarEnergyCreatorName\n" +
                "from tb_solar_fault f,tb_org o,tb_org o2,tb_solar_energy p,tb_user u\n" +
                "where f.SolarOrgId = o.OrgId and p.SolarEnergyMainOrgId=o2.orgId and f.SolarEnergyId = p.SolarEnergyId  AND\n" +
                "f.SolarCreatorId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.SolarTime between '" + dateStr[0] + "' and '" + dateStr[1]+1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.SolarOrgId IN (" + orgIds + ") or " +
                    "f.SolarMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY f.SolarTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        List<SolarEnergyFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getFaultCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_solar_fault f,tb_org o WHERE f.SolarOrgId= o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.SolarTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.SolarOrgId IN (" + orgIds + ") or " +
                    "f.SolarMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SolarEnergyFaultDto> getDevice4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select m.SolarFaultId,m.SolarPhoto,m.SolarMaintenancePhoto,m.SolarMaintenanceStatus,m.SolarMaintenanceTime,\n" +
                "o.OrgName AS solarEnergyOrgName,m.SolarEnergyId,s.SolarEnergyRoadName,s.SolarEnergyEndRoad,s.SolarEnergyStartRoad,s.SolarEnergyArea,solarMoney,\n" +
                "s.SolarEnergyCity,s.SolarEnergyProvince,o2.orgName as solarEnergyMainOrgName,s.SolarEnergyPhoto,s.solarEnergyPeriod,u.UserName AS solarEnergyCreatorName\n" +
                "from tb_solar_fault m,tb_user u,tb_org o,tb_org o2,tb_solar_energy s \n" +
                "where m.SolarMaintenanceStatus=2 and m.SolarMaintainerId = u.userId and s.SolarEnergyMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SolarEnergyId = s.SolarEnergyId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.SolarMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.SolarMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        List<SolarEnergyFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getDeviceCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select count(*) from tb_solar_fault m,tb_user u,tb_org o,tb_org o2,tb_solar_energy s \n" +
                "where m.SolarMaintenanceStatus=2 and m.SolarMaintainerId = u.userId and s.SolarEnergyMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SolarEnergyId = s.SolarEnergyId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.SolarMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.SolarMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<Integer> getSolarEnergyTypeCounts(String orgIds, String time, String province, String city, String area, String road) throws Exception{
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select r.SolarEnergyType from tb_solar_energy r  where (r.SolarEnergyOrgId in (" + orgIds + ") or " +
                "r.SolarEnergyMainOrgId in (" + orgIds + ")) AND (r.SolarEnergyInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and r.SolarEnergyProvince like '%" + province + "%' and r.SolarEnergyCity like '%" + city + "%' " +
                " and r.SolarEnergyArea like '%" + area + "%' and r.SolarEnergyRoadName like '%" + road + "%'   ORDER BY r.SolarEnergyType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }

    public Integer getSolarCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_solar_energy where SolarEnergyId like '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SolarEnergyProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SolarEnergyCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SolarEnergyArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and SolarEnergyRoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (SolarEnergyStartRoad ='" + sQuery[4] + "' or SolarEnergyEndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (SolarEnergyEndRoad ='" + sQuery[5] + "' or SolarEnergyStartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((SolarEnergyStartRoad ='" + sQuery[4] + "' and SolarEnergyEndRoad ='" + sQuery[5] + "') or" +
                    "(SolarEnergyStartRoad = '" + sQuery[5] + "' and SolarEnergyEndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SolarEnergyMainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and SolarEnergyOrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (SolarEnergyOrgId IN (" + orgIds + ") or " +
                    "SolarEnergyMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SolarEnergyDto> getSolar4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select se.SolarEnergyId,se.SolarEnergyPhoto,se.SolarEnergyProvince,se.SolarEnergyCity,se.SolarEnergyArea," +
                "se.SolarEnergyRoadName,m.OrgName as solarEnergyMainOrgName,o.OrgName as solarEnergyOrgName,se.SolarEnergyLongitude," +
                "se.SolarEnergyLatitude,se.SolarEnergyType,se.SolarEnergyIsComplete,se.SolarEnergyMaterial,se.SolarEnergyInstallTime," +
                "se.SolarEnergyStartRoad,se.SolarEnergyEndRoad from tb_solar_energy se " +
                "LEFT JOIN tb_org m ON se.SolarEnergyMainOrgId = m.OrgId " +
                "LEFT JOIN tb_org o ON se.SolarEnergyOrgId = o.OrgId " +
                "where se.SolarEnergyId LIKE '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SolarEnergyProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SolarEnergyCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SolarEnergyArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and SolarEnergyRoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (SolarEnergyStartRoad ='" + sQuery[4] + "' or SolarEnergyEndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (SolarEnergyEndRoad ='" + sQuery[5] + "' or SolarEnergyStartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((SolarEnergyStartRoad ='" + sQuery[4] + "' and SolarEnergyEndRoad ='" + sQuery[5] + "') or" +
                    "(SolarEnergyStartRoad = '" + sQuery[5] + "' and SolarEnergyEndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SolarEnergyMainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and SolarEnergyOrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (SolarEnergyOrgId IN (" + orgIds + ") or " +
                    "SolarEnergyMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY se.SolarEnergyInstallTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        List<SolarEnergyDto> solarEnergyDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarEnergyDtos;
    }

    public List<String> getProvinceName(String orgIds) throws Exception {
        String hql = "select distinct se.SolarEnergyProvince from tb_solar_energy se where se.SolarEnergyOrgId in (" + orgIds + ") or " +
                "se.SolarEnergyMainOrgId in (" + orgIds + ")";
        hql += " order by convert(se.SolarEnergyProvince using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> province = query.getResultList();
        return province;
    }

    public List<String> getCityName(String orgIds, String provinceName) throws Exception {
        String hql = "select distinct se.SolarEnergyCity from tb_solar_energy se where (se.SolarEnergyOrgId in (" + orgIds + ") or " +
                "se.SolarEnergyMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and se.SolarEnergyProvince = '" + provinceName + "'";
        }
        hql += " order by convert(se.SolarEnergyCity using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> city = query.getResultList();
        return city;
    }

    public List<String> getAllAreaName(String orgIds, String provinceName, String cityName) throws Exception {
        String hql = "select distinct se.SolarEnergyArea from tb_solar_energy se where (se.SolarEnergyOrgId IN (" + orgIds + ")" +
                " or se.SolarEnergyMainOrgId IN (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and se.SolarEnergyProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and se.SolarEnergyCity ='" + cityName + "'";
        }
        hql += " order by convert(se.SolarEnergyArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> area = query.getResultList();
        return area;
    }

    public List<String> getRoadNames(String provinceName, String cityName, String areaName, String orgId, Integer actor) throws Exception {
        String hql = "select distinct se.SolarEnergyRoadName from tb_solar_energy se where (se.SolarEnergyOrgId in (" + orgId + ") or " +
                "se.SolarEnergyMainOrgId in (" + orgId + "))";
        if (!provinceName.equals("-1")) {
            hql += " and se.SolarEnergyProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and se.SolarEnergyCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and se.SolarEnergyArea = '" + areaName + "'";
        }
        hql += " order by convert(se.SolarEnergyRoadName using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getStartRoadName(String orgIds, String provinceName, String cityName, String areaName,
                                         String endRoadName, String roadName) throws Exception {
        String hql = "select distinct se.SolarEnergyStartRoad from tb_solar_energy se where (se.SolarEnergyOrgId in (" + orgIds + ") or " +
                "se.SolarEnergyMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and se.SolarEnergyProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and se.SolarEnergyCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and se.SolarEnergyArea = '" + areaName + "'";
        }
        if (!endRoadName.equals("-1")) {
            hql += " and se.SolarEnergyEndRoad = '" + endRoadName + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and se.SolarEnergyRoadName = '" + roadName + "'";
        }
        hql += " order by convert(se.SolarEnergyStartRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getEndRoadName(String orgIds, String startRoad, String provinceName, String cityName,
                                       String areaName, String roadName) throws Exception {
        String hql = "select distinct se.SolarEnergyEndRoad from tb_solar_energy se where (se.SolarEnergyOrgId in (" + orgIds + ") or " +
                "se.SolarEnergyMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and se.SolarEnergyProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and se.SolarEnergyCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and se.SolarEnergyArea = '" + areaName + "'";
        }
        if (!startRoad.equals("-1")) {
            hql += " and se.SolarEnergyStartRoad = '" + startRoad + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and se.SolarEnergyRoadName = '" + roadName + "'";
        }
        hql += " order by convert(se.SolarEnergyEndRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }


}
